set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_oper_effect_sign_summary_dt partition(dt)
select
     wide.sign_date          as sign_date           --揽收日期
    ,wide.sign_network_code  as sign_network_code   --签收网点编码
    ,wide.sign_network_name  as sign_network_name   --签收网点名称
    ,wide.sign_fran_code     as sign_fran_code      --签收加盟商编码
    ,wide.sign_fran_name     as sign_fran_name      --签收加盟商名称
    ,wide.sign_agent_code    as sign_agent_code     --签收代理区编码
    ,wide.sign_agent_name    as sign_agent_name     --签收代理区名称
    ,wide.sign_regional_id   as sign_regional_code  --签收大区编码
    ,wide.sign_regional_desc as sign_regional_desc  --签收大区名称
    ,wide.sign_area_id       as sign_area_code      --签收区县编码
    ,wide.sign_area_desc     as sign_area_desc      --签收区县名称
    ,wide.sign_city_id       as sign_city_code      --签收城市编码
    ,wide.sign_city_desc     as sign_city_desc      --签收城市名称
    ,wide.sign_provider_id   as sign_provider_code  --签收省份编码
    ,wide.sign_provider_desc as sign_provider_desc  --签收省份名称
    ,wide.ordersource_code   as ordersource_code    --订单来源编码
    ,wide.ordersource_name   as ordersource_name    --订单来源名称
    ,wide.deliver_user_code  as deliver_user_code   --出仓员编码
    ,wide.deliver_user_name  as deliver_user_name   --出仓员名称
    ,round(sum(wide.real_pre_sign_difftime),2) as real_pre_sign_time --实际签收和时效签收时间差
    ,sum(case when wide.real_pre_sign_difftime>0 then 1 else 0 end) as real_pre_sign_cnt --实际签收和时效签收票数
    ,wide.sign_date as dt --签收日期
from jms_dws.dws_wide_unsign_whole_effect_dt wide
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and wide.send_network_code is not null
  and wide.sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
  and wide.if_reback    <> 1
  and wide.if_end_piece <> 1
  and wide.if_intercept <> 1
group by wide.sign_date           --签收日期
        ,wide.sign_network_code   --签收网点编码
        ,wide.sign_network_name   --签收网点名称
        ,wide.sign_fran_code      --签收加盟商code
        ,wide.sign_fran_name      --签收加盟商名称
        ,wide.sign_agent_code     --签收代理区code
        ,wide.sign_agent_name     --签收代理区名称
        ,wide.sign_regional_id    --签收大区id
        ,wide.sign_regional_desc  --签收大区名称
        ,wide.ordersource_code    --订单来源编码
        ,wide.sign_area_id        --签收区县id
        ,wide.sign_area_desc      --签收区县名称
        ,wide.sign_city_id        --签收城市id
        ,wide.sign_city_desc      --签收城市名称
        ,wide.sign_provider_id    --签收省份id
        ,wide.sign_provider_desc  --签收省份名称
        ,wide.ordersource_name    --订单来源名称
        ,wide.deliver_user_code   --出仓员编码
        ,wide.deliver_user_name   --出仓员名称
distribute by dt,pmod(hash(rand()),10)
;



